package foodhelper.database;

import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import foodhelper.model.Food;
import foodhelper.model.FoodType;
import foodhelper.model.Nutrition;
import foodhelper.model.Tip;



public class Database {
	private static String TABLE_FOODTYPE = "FoodType";
	private static String TYPEID = "typeId";
	private static String TYPENAME = "typeName";
	
	private static String TABLE_FOOD = "Food";
	private static String FOODID = "foodId";
	private static String FOODNAME = "foodName";
	private static String FOODINGREDIENT ="foodIngredient";
	private static String FOODTYPE = "foodType";
	
	private static String TABLE_COMPONENTFOOD = "componentfood";
	private static String QUANTITY = "quantity";
	
	private static String TABLE_NUTRITION = "Nutrition";
	private static String NID = "nId";
	private static String NINGREDIENT = "nIngredient";
	private static String NNUTRITION = "nNutrition";
	private static String NCALORIES = "nCalories";
	
	private static String TABLE_TIPS = "Tips";
	private static String TIPSID = "tipsId";
	private static String TIPSTITLE = "tipsTitle";
	private static String TIPSCONTENT = "tipsContent";
	private static String TIPSREF = "tipsRef";
	
	private DatabaseHelper mDbhepler;
	private SQLiteDatabase mDb;
	private Context mContext;
	
	private static final String DATABASE_FOODTYPE = "create table if not exists "
			+ TABLE_FOODTYPE
			+ "("
			+ TYPEID + " TEXT PRIMARY KEY NOT NULL,"
			+ TYPENAME + " TEXT NOT NULL"
			+ ");";
	
	private static final String DATABASE_FOOD = "create table if not exists "
			+ TABLE_FOOD
			+ "("
			+ FOODID + " INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL ,"
			+ FOODNAME + " TEXT  NOT NULL ,"
			+ FOODINGREDIENT + " TEXT  NOT NULL ,"
			+ FOODTYPE + " TEXT  NOT NULL ,"
			+ "FOREIGN KEY(" + "FOODTYPE" + ") REFERENCES " + "TABLE_FOODTYPE" + "(" + TYPEID + "))"
			+ ");";
	
	private static final String DATABASE_COMPONENT = "create table if not exists "
			+ TABLE_COMPONENTFOOD
			+ "("
			+ FOODID + "INTEGER NOT NULL, "
			+ NID + "INTEGER NOT NULL, "
			+ QUANTITY + "INTEGER NOT NULL"
			+ ");";
	
	private static final String DATABASE_NUTRITION = "create table if not exists "
			+ TABLE_NUTRITION
			+ "("
			+ NID + " INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,"
			+ NINGREDIENT + " TEXT  NOT NULL,"
			+ NNUTRITION + " TEXT NOT NULL,"
			+ NCALORIES + " INTEGER  NOT NULL"
			+");";
	
	
	private static final String DATABASE_TIPS = "create table if not exists "
			+ TABLE_TIPS
			+ "("
			+ TIPSID + " INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,"
			+ TIPSTITLE + " TEXT  NOT NULL,"
			+ TIPSCONTENT + " TEXT NOT NULL,"
			+ TIPSREF + " INTEGER  NOT NULL"
			+");";
	private static final int DATABASE_VERSION = 1;

	private static Database instance;

	public static Database getInstance(Context context) {
		if (instance == null) {
			instance = new Database(context);
			instance.open();
		}

		return instance;
	}

	private Database(Context ct) {
		this.mContext = ct;
	}

	public class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_FOODTYPE);
			db.execSQL(DATABASE_FOOD);
			db.execSQL(DATABASE_NUTRITION);
			db.execSQL(DATABASE_TIPS);
			
		}

		@Override
		public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
			// TODO Auto-generated method stub
		}

	}

	private Database open() {
		mDbhepler = new DatabaseHelper(mContext, "DataFoodHelper.db", null, DATABASE_VERSION);
		mDb = mDbhepler.getWritableDatabase();
		return this;
	}

	public void addDataFoodType(String typeId, String typeName) {
		ContentValues value = new ContentValues();
		value.put(TYPEID, typeId);
		value.put(TYPENAME, typeName);
		mDb.insert(TABLE_FOODTYPE, null, value);
	}
	public void addDataFood(int foodId, String foodName, String foodIngredient, String foodType) {
		ContentValues value = new ContentValues();
		value.put(FOODID, foodId);
		value.put(FOODNAME, foodName);
		value.put(FOODINGREDIENT, foodIngredient);
		value.put(FOODTYPE, foodType);
		mDb.insert(TABLE_FOOD, null, value);
	}
	public void addComponentFood(int foodId, int nutritionId, int quantity) {
		ContentValues value = new ContentValues();
		value.put(FOODID, foodId);
		value.put(NID, nutritionId);
		value.put(QUANTITY, quantity);
		mDb.insert(DATABASE_COMPONENT, null, value);
	}
	public void addDataNutrition(int nId, String nIngredient, String nNutrition, int nCalories) {
		ContentValues value = new ContentValues();
		value.put(NID, nId);
		value.put(NINGREDIENT, nIngredient);
		value.put(NNUTRITION, nNutrition);
		value.put(NCALORIES, nCalories);
		mDb.insert(TABLE_NUTRITION, null, value);
	}
	public void addDataTIPS(int tipsId, String tipsTitle, String tipsContent, String tipsRef) {
		ContentValues value = new ContentValues();
		value.put(TIPSID, tipsId);
		value.put(TIPSTITLE, tipsTitle);
		value.put(TIPSCONTENT, tipsContent);
		value.put(TIPSREF, tipsRef);
		mDb.insert(TABLE_TIPS, null, value);
	}
	public void loadData(){
		addDataFoodType("AS", "Ăn Sáng");
		addDataFoodType("AC","Ăn Chính");
		addDataFoodType("TM", "Tráng Miệng");
		addDataFoodType("NU", "Nước Uống");
		
		addDataFood(1 , "Bò Xào", "bò,hành,carot,dưa","AC");
		addDataFood(2 , "Phở", "gạo,thịt","AS");
		addDataFood(3 , "đậu xốt rau củ", "đậu phụ, thịt xay, đậu Hà Lan, cà rốt, tỏi","AS");
		addDataFood(4 , "canh chua", "giá, me, bạc hà, rau ôm","AS");
		addDataFood(5 ,"thịt kho", "thịt, tiu","AS");
		
		addDataNutrition( 100, "bo", "B1B3DC",52 );
		addDataNutrition( 101, "gao", "D1D4",100 );
		addDataNutrition( 102, "hành", "",0 );
		addDataNutrition( 103, "carot", "",0 );
		addDataNutrition( 104, "dua", "",0 );
		
		addDataTIPS(1,"Uống bao nhi�?u nước là đủ","Theo như......","Dinh Dưỡng H�?c �?ư�?ng");
		addDataTIPS(2,"Vận động bao nhiêu là đủ","Một ngày bạn nên tiêu hao ít nhất","Dinh Dưỡng H�?c �?ư�?ng");
		
	}
	public void UpdateDataTIPS(Tip tip) {
		ContentValues value = new ContentValues();
		value.put(TIPSID, tip.getTipsId());
		value.put(TIPSTITLE, tip.getTipsTitle());
		value.put(TIPSCONTENT, tip.getTipsContent());
		value.put(TIPSREF, tip.getTipsRef());
		mDb.update(TABLE_TIPS, value, TIPSID + "=?", new String[] { tip.getTipsId()	+ "" });
	}
	
	public ArrayList<FoodType> getListFoodType() {
		ArrayList<FoodType> listFoodType = new ArrayList<FoodType>();
		Cursor cur = mDb.query(TABLE_FOODTYPE, new String[] { TYPEID, TYPENAME}, null , null, null, null, null);//Giam///
		if (cur != null) {
			if (cur.moveToFirst()) {
				do {
					FoodType foodType = new FoodType();
					foodType.setTypeId(cur.getString(0));
					foodType.setTypeName(cur.getString(1));
					listFoodType.add(foodType);
				}while (cur.moveToNext());
			}
		}
		return listFoodType;
	}
	public ArrayList<Food> getListFood() {
		ArrayList<Food> listFood = new ArrayList<Food>();
		Cursor cur = mDb.query(TABLE_FOODTYPE, new String[] { FOODID, FOODNAME, FOODINGREDIENT, FOODTYPE}, null , null, null, null, null);//Giam///
		if (cur != null) {
			if (cur.moveToFirst()) {
				do {
					Food food = new Food();
					food.setFoodId(cur.getInt(0));
					food.setFoodName(cur.getString(1));
					food.setFoodIngredient(cur.getString(2));
					food.setFoodType(cur.getString(3));
					listFood.add(food);
				}while (cur.moveToNext());
			}
		}
		return listFood;
	}
	public ArrayList<Nutrition> getListNutrition() {
		ArrayList<Nutrition> listNutrition = new ArrayList<Nutrition>();
		Cursor cur = mDb.query(TABLE_FOODTYPE, new String[] { NID, NINGREDIENT, NNUTRITION, NCALORIES}, null , null, null, null, null);//Giam///
		if (cur != null) {
			if (cur.moveToFirst()) {
				do {
					Nutrition nutrition = new Nutrition();
					nutrition.setnId(cur.getInt(0));
					nutrition.setnIngredient(cur.getString(1));
					nutrition.setnNutrition(cur.getString(2));
					nutrition.setnCalories(cur.getInt(3));
					listNutrition.add(nutrition);
				}while (cur.moveToNext());
			}
		}
		return listNutrition;
	}
	public ArrayList<Tip> getListTIPS() {
		ArrayList<Tip> listTips = new ArrayList<Tip>();
		Cursor cur = mDb.query(TABLE_FOODTYPE, new String[] { TIPSID, TIPSTITLE, TIPSCONTENT, TIPSREF}, null , null, null, null, null);//Giam///
		if (cur != null) {
			if (cur.moveToFirst()) {
				do {
					Tip tips = new Tip();
					tips.setTipsId(cur.getInt(0));
					tips.setTipsTitle(cur.getString(1));
					tips.setTipsContent(cur.getString(2));
					tips.setTipsRef(cur.getString(3));
					listTips.add(tips);
				}while (cur.moveToNext());
			}
		}
		return listTips;
	}
}
